package com.apes.autoparts.supplier.contrast;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.springframework.stereotype.Component;

import java.util.Date;
import java.util.Map;

/***
 * 对照数据dao层（直接与erp通过同义词进行访问）
 */
@Component
public class ContrastDao extends ApesDao {

    /*** ======================================查找已编译的数据，用于生成待办=====================================================  ***/

    /***
     * 供应商 + 供应商零件编码
     */
    public JSONArray findOneContrastByOem(Map param) {

        String sSQL = "SELECT A.BUSINESS_ID, B.CONTRAST_BRAND_ID, B.CONTRAST_ORIGIN,\n" +
                "        B.CONTRAST_PART_ID, B.CATEGORY_ID\n" +
                "   FROM MAS_BUSINESSINFO A\n" +
                "   LEFT JOIN MAS_BUSINESSINFO_CONTRAST B\n" +
                "     A.ID = B.BUSINESSINFO_ID\n" +
                "  WHERE A.SUPPLIER_ID = :SUPPLIER_ID\n" +
                "    AND A.BUSINESS_ID = :BUSINESS_ID";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 平台供应商 + 平台原始码
     */
    public JSONArray findOneContrastBySource(Map param) {


        String sSQL = "SELECT A.BUSINESS_ID, B.CONTRAST_BRAND_ID, B.CONTRAST_ORIGIN,\n" +
                "       B.CONTRAST_PART_ID, B.CATEGORY_ID\n" +
                "  FROM MAS_BUSINESSINFO A\n" +
                "  LEFT JOIN MAS_BUSINESSINFO_CONTRAST B\n" +
                "    ON A.ID = B.BUSINESSINFO_ID\n" +
                " WHERE A.SUPPLIER_PLATFORM_ITEM_ID = :SUPPLIER_PLATFORM_ITEM_ID\n" +
                "   AND A.SUPPLIER_PLATFORM_SOURCE = :SUPPLIER_PLATFORM_SOURCE" ;

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 平台供应商 + 平台原始码
     */
    public JSONArray findOneBUSINESSINFOBySource(Map param) {

        String sSQL = "SELECT A.BRAND_NAME,A.PART_NAME, A.UOM_NAME, A.INVENTORY_QUANTITY, A.UNTAX_PRICE," +
                " A.ORIGIN ,A.BUSINESS_ID, A.ONSHELVES, " +
                "B.CONTRAST_BRAND_ID, B.CONTRAST_ORIGIN,\n" +
                "B.CONTRAST_BUSINESS_ID,B.CONTRAST_BRAND_NAME,   B.CONTRAST_PART_ID, B.CATEGORY_ID\n" +
                "  FROM MAS_BUSINESSINFO A\n" +
                "  LEFT JOIN MAS_BUSINESSINFO_CONTRAST B\n" +
                "    ON A.ID = B.BUSINESSINFO_ID\n" +
                " WHERE A.SUPPLIER_PLATFORM_ITEM_ID = :SUPPLIER_PLATFORM_ITEM_ID \n" +
                "   AND A.SUPPLIER_PLATFORM_SOURCE = :SUPPLIER_PLATFORM_SOURCE " +
                "   AND B.SUPPLIER_ID = :SUPPLIER_ID ";

        return toJSONArray(find(sSQL, param));
    }

    /*** ======================================查找已编译的数据，用于生成待办=====================================================  ***/






    /*** ======================================查找数据，用于更新供应商对照=====================================================  ***/


    /***
     * 查找一行供应商数据
     */
    public JSONArray findOneBusinessInfo(Map param) {
        String sSQL = "SELECT A.ID, A.BUSINESS_ID\n" +
                "  FROM MAS_BUSINESSINFO A\n" +
                " WHERE A.SUPPLIER_PLATFORM_ITEM_ID = :SUPPLIER_PLATFORM_ITEM_ID\n" +
                "   AND A.SUPPLIER_PLATFORM_SOURCE = :SUPPLIER_PLATFORM_SOURCE" +
                "   AND A.SUPPLIER_ID =:SUPPLIER_ID";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 创建一行供应商数据
     */
    public JSONArray createOneBusinessInfo(Map param) {
        String sSQL = "INSERT INTO MAS_BUSINESSINFO\n" +
                "  (ID, SUPPLIER_PLATFORM_ITEM_ID, SUPPLIER_PLATFORM_SOURCE, SUPPLIER_ID, SUPPLIER_NAME, BUSINESS_ID, LRR, LRRQ_SJ, TS01, PART_NAME," +
                "BRAND_NAME, UOM_NAME, ORIGIN, INVENTORY_QUANTITY, UNTAX_PRICE)\n" +
                "VALUES\n" +
                "  (:ID, :SUPPLIER_PLATFORM_ITEM_ID, :SUPPLIER_PLATFORM_SOURCE, :SUPPLIER_ID, :SUPPLIER_NAME, :BUSINESS_ID, 'admin', SYSDATE, '"+ DateUtil.format(new Date(), "yyyyMMdd.HHmmss.SSS") +"'" +
                ",:PART_NAME, :BRAND_NAME, :UOM_NAME, :ORIGIN, :INVENTORY_QUANTITY, :UNTAX_PRICE)";
        insert(sSQL, param);

        return findOneBusinessInfo(param);
    }


    /***
     * 查找一行供应商对照
     */
    public JSONArray findOneBusinessContrast(Map param) {
        String sSQL = "SELECT A.CONTRAST_BUSINESS_ID\n" +
                "  FROM MAS_BUSINESSINFO_CONTRAST A\n" +
                " WHERE A.BUSINESSINFO_ID = :ID" +
                "  AND  A.SUPPLIER_ID = :SUPPLIER_ID\n";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 创建一行供应商对照
     */
    public JSONArray createOneBusinessContrast(Map param) {
        String sSQL = "INSERT INTO MAS_BUSINESSINFO_CONTRAST\n" +
                "  (BUSINESSINFO_ID, CONTRAST_BUSINESS_ID, SUPPLIER_ID, SUPPLIER_NAME, LRR, LRRQ_SJ, TS01, CONTRAST_BRAND_ID, CONTRAST_BRAND_NAME, CONTRAST_ORIGIN, " +
                " CONTRAST_PART_ID, CATEGORY_ID)\n" +
                "VALUES\n" +
                "  (:ID, :BUSINESS_ID, :SUPPLIER_ID, :SUPPLIER_NAME, 'admin', SYSDATE, '"+ DateUtil.format(new Date(), "yyyyMMdd.HHmmss.SSS") +"'" +
                ", :CONTRAST_BRAND_ID, :CONTRAST_BRAND_NAME, :CONTRAST_ORIGIN,  :CONTRAST_PART_ID, :CATEGORY_ID)";
        insert(sSQL, param);
        return findOneBusinessContrast(param);
    }

    /***
     * 更新供应商数据(包含库存和价格)
     */
    public void updateBusinessInfo(Map param) {
        String sSQL = "UPDATE MAS_BUSINESSINFO A\n" +
                "   SET A.PART_NAME = :PART_NAME, A.BRAND_NAME = :BRAND_NAME,\n" +
                "       A.UOM_NAME = :UOM_NAME, A.ORIGIN = :ORIGIN,\n" +
                "       A.TS01 = '"+ DateUtil.format(new Date(), "yyyyMMdd.HHmmss.SSS") +"',\n" +
                "       A.INVENTORY_QUANTITY = :INVENTORY_QUANTITY,\n" +
                "       A.UNTAX_PRICE = :UNTAX_PRICE,\n" +
                "       A.BUSINESS_ID = :BUSINESS_ID\n" +
                " WHERE A.ID = :ID " +
                "       AND A.SUPPLIER_ID = :SUPPLIER_ID";
        update(sSQL, param);
    }


    public void updateSupplierData(JSONObject param) {
        String sSQL = "UPDATE MAS_BUSINESSINFO A \n" +
                "                   SET A.PART_NAME = :partCodeName, A.BRAND_NAME = :brandName, \n" +
                "                       A.UOM_NAME = :unit, A.ORIGIN = :originPlace, \n" +
                "                       A.INVENTORY_QUANTITY = :stockQty, \n" +
                "                       A.UNTAX_PRICE = :untaxPrice, \n" +
                "                       A.BUSINESS_ID = :partCode " +
                "                 WHERE A.SUPPLIER_PLATFORM_ITEM_ID = :supplierPlatformItemId\n" +
                "                 AND   A.SUPPLIER_PLATFORM_SOURCE = :source";
        update(sSQL, param);
    }
    /***
     * 更新状态为下架
     */
    public void updateBusinessInfoOnshelves(Map param) {
        String sSQL = "UPDATE MAS_BUSINESSINFO A SET A.ONSHELVES = '下架' WHERE A.ID = :ID";
        update(sSQL, param);
    }

    /***
     * 更新对照信息
     */
    public void updateBusinessContrast(Map param) {
        String sSQL = " UPDATE MAS_BUSINESSINFO_CONTRAST A\n" +
                "    SET A.CONTRAST_BRAND_ID = :CONTRAST_BRAND_ID,\n" +
                "        A.CONTRAST_BRAND_NAME = :CONTRAST_BRAND_NAME,\n" +
                "        A.CONTRAST_ORIGIN = :CONTRAST_ORIGIN,\n" +
                "        A.CONTRAST_BUSINESS_ID = :BUSINESS_ID,\n" +
                "        A.CONTRAST_PART_ID = :CONTRAST_PART_ID,\n" +
                "        A.CATEGORY_ID = :CATEGORY_ID,\n" +
                "        A.TS01 = '"+ DateUtil.format(new Date(), "yyyyMMdd.HHmmss.SSS") +"'\n" +
                "  WHERE A.BUSINESSINFO_ID = :ID" +
                "  AND A.SUPPLIER_ID = :SUPPLIER_ID";
        update(sSQL, param);
    }

    /**
     * 查询多行供应商数据
     * @param param
     * @return
     */
    public JSONArray findInsertBusinessInfos(JSONObject param) {
        String sSQL =
                " SELECT A.ID, A.SUPPLIER_PLATFORM_SOURCE, A.BUSINESS_ID, B.CONTRAST_BRAND_NAME, B.CONTRAST_BRAND_ID, B.CONTRAST_ORIGIN, B.CONTRAST_PART_ID, B.CATEGORY_ID \n" +
                " FROM MAS_BUSINESSINFO A LEFT JOIN MAS_BUSINESSINFO_CONTRAST B ON A.ID = B.BUSINESSINFO_ID " +
                " WHERE A.SUPPLIER_PLATFORM_ITEM_ID = :SUPPLIER_PLATFORM_ITEM_ID " +
                " AND A.SUPPLIER_PLATFORM_SOURCE \n" +
                " IN :SUPPLIER_PLATFORM_SOURCES ";

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 获取增量商家数据
     * @param param
     * @return
     */
    public String getIncrementBusinessInfo(JSONObject param) {
        String sSQL = "   SELECT A.BUSINESS_ID, A.BRAND_NAME, A.BRAND_MODEL, A.PART_NAME, A.UOM_NAME, " +
                "       A.UNIVERSAL_PART_NUMBER, A.PRODUCT_DESCRIPTION, A.INVENTORY_QUANTITY, " +
                "       A.UNTAX_PRICE, ONSHELVES, A.SUPPLIER_ID, A.SIZES, (CASE WHEN A.TS01 > B.TS01 THEN A.TS01 ELSE B.TS01 END) TS01," +
                "       A.ORIGIN, A.SUPPLIER_NAME, A.LRR, to_date(A.LRRQ_SJ) LRRQ_SJ, A.ID, " +
                "        A.QUALITY_NAME, A.BZHL, " +
                "       B.CONTRAST_BUSINESS_ID, B.CONTRAST_BRAND_NAME, B.CONTRAST_ORIGIN, " +
                "       B.CONTRAST_PART_ID, B.BUSINESSINFO_ID, B.CONTRAST_BRAND_ID, " +
                "       B.CATEGORY_ID " +
                "  FROM MAS_BUSINESSINFO A, MAS_BUSINESSINFO_CONTRAST B " +
                "    WHERE A.ID = B.BUSINESSINFO_ID " ;
        if (param.get("conditions") == null) {
            sSQL += " AND (A.TS01 > :TIMESTAMP OR B.TS01 > :TIMESTAMP)" ;
        } else {
            sSQL += " AND (((A.TS01 = :TIMESTAMP OR B.TS01 = :TIMESTAMP)  AND ID > :conditions) or (A.TS01 > :TIMESTAMP OR B.TS01 > :TIMESTAMP)) " +
                    " ORDER BY A.ID, A.TS01, B.TS01 " ;

        }
        return sSQL;
    }

    public JSONArray getBusinessinfoPage(JSONObject param) {

        String sSQL = "SELECT G.*\n" +
                "  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE\n" +
                "           FROM (SELECT MAX(ROWNUM) OVER() TOTAL, G.*\n" +
                "                    FROM ("+ getIncrementBusinessInfo(param) +"\n" +
                "                   ) G\n" +
                "          WHERE ROWNUM <= DECODE(0, :PAGE, :SIZE, (:PAGE + 1) * :SIZE)) G) G\n" +
                " WHERE RN > (:PAGE) * :SIZE" ;
        return toJSONArray(find(sSQL, param));
    }

}
